package com.boss.salary.dao.impl;

import com.boss.framework.dao.BaseDaoImpl;
import com.boss.salary.constant.TableName;
import com.boss.salary.dao.ISalPostStandardSalaryDao;
import com.boss.salary.entity.SalPostStandardSalary;
import com.boss.salary.entity.SalaryMonthEnd;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@Repository
public class SalPostStandardSalaryDaoImpl extends BaseDaoImpl implements ISalPostStandardSalaryDao {

    @Override
    public List<Map<String, Object>> querySalPostStandardSalaryAll(Map<String, Object> queryParam) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" select a.id,a.grade_level_id gradeLevelId,b.`name` gradeLevelName,a.cascade_code cascadeCode, ")
                .append(" a.type,a.salary_id salaryId,a.salary_money salaryMoney,a.mof_div_code mofDivCode,a.create_time createTime, ")
                .append(" a.update_time updateTime,c.id personTypeId,c.`name` personTypeName,d.id postId,d.`name` postName from ")
                .append(TableName.SAL_POST_STANDARD_SALARY)
                .append(" a LEFT JOIN ").append(TableName.SAL_POST_GRADE_LEVEL)
                .append(" b on a.grade_level_id = b.id LEFT JOIN ").append(TableName.SAL_PERSON_TYPE)
                .append(" c on b.person_type_id = c.id LEFT JOIN ").append(TableName.SAL_POST)
                .append(" d on b.post_id = d.id where mof_div_code = ? ");
        paramList.add(queryParam.get("mofDivCode"));
        if (!StringUtils.isEmpty(queryParam.get("type"))) {
            sql.append(" and a.type = ? ");
            paramList.add(queryParam.get("type"));
        }
        if (!StringUtils.isEmpty(queryParam.get("salaryId"))) {
            sql.append(" and a.salary_id = ? ");
            paramList.add(queryParam.get("salaryId"));
        }
        if (!StringUtils.isEmpty(queryParam.get("gradeLevelId"))) {
            sql.append(" and a.grade_level_id = ? ");
            paramList.add(queryParam.get("gradeLevelId"));
        }
        sql.append(" order by c.code IS NULL,c.code,d.code,b.code");
        return this.getjdbcTemplate().queryForList(sql.toString(), paramList.toArray());
    }
    @Override
    public List<Map<String, Object>> getSalByGradeLevelId(Map<String, Object> queryParam) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" select a.id,a.grade_level_id gradeLevelId,b.`name` gradeLevelName,a.cascade_code cascadeCode, ")
                .append(" a.type,a.salary_id salaryId,a.salary_money salaryMoney,a.mof_div_code mofDivCode,a.create_time createTime, ")
                .append(" a.update_time updateTime,c.id personTypeId,c.`name` personTypeName,d.id postId,d.`name` postName from ")
                .append(TableName.SAL_POST_STANDARD_SALARY)
                .append(" a LEFT JOIN ").append(TableName.SAL_POST_GRADE_LEVEL)
                .append(" b on a.grade_level_id = b.id LEFT JOIN ").append(TableName.SAL_PERSON_TYPE)
                .append(" c on b.person_type_id = c.id LEFT JOIN ").append(TableName.SAL_POST)
                .append(" d on b.post_id = d.id where mof_div_code = ? ");
        paramList.add(queryParam.get("mofDivCode"));
        sql.append(" and a.grade_level_id = ? ");
        paramList.add(queryParam.get("gradeLevelId"));
        if (!StringUtils.isEmpty(queryParam.get("type"))) {
            sql.append(" and a.type = ? ");
            paramList.add(queryParam.get("type"));
        }

        sql.append(" order by a.create_time ");
        return this.getjdbcTemplate().queryForList(sql.toString(), paramList.toArray());
    }

    @Override
    public int checkRepeat(Map<String, String> queryParams) {
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT COUNT(*) FROM ").append(TableName.SAL_POST_STANDARD_SALARY).append(" spss ")
                .append(" JOIN ").append(TableName.SAL_POST_GRADE_LEVEL).append(" spgl ")
                .append(" ON spss.grade_level_id = spgl.id ");
        sql.append(" WHERE salary_id = ? ")
                .append(" AND person_type_id = ? ")
                .append(" AND post_id = ? ")
                .append(" AND grade_level_id = ? ")
                .append(" AND spss.type = ? ");
        Object[] objects = new Object[] {
                queryParams.get("salaryId"), queryParams.get("personTypeId"), queryParams.get("postId"),
                queryParams.get("gradeLevelId"), queryParams.get("type")
        };
        return this.getjdbcTemplate().queryForObject(sql.toString(), objects, Integer.class);
    }

    @Override
    public void insertSalPostStandardSalary(SalPostStandardSalary SalPostStandardSalary) {
        StringBuilder sql = new StringBuilder();
        sql.append(" insert into ").append(TableName.SAL_POST_STANDARD_SALARY);
        sql.append(" (id, create_time, update_time, grade_level_id, cascade_code, type, salary_id, salary_money, mof_div_code) ")
                .append(" value (?,?,?,?,?,?,?,?,?) ");
        Object[] params = new Object[]{
                SalPostStandardSalary.getId(), SalPostStandardSalary.getCreateTime(), SalPostStandardSalary.getUpdateTime(),
                SalPostStandardSalary.getGradeLevelId(), SalPostStandardSalary.getCascadeCode(), SalPostStandardSalary.getType(),
                SalPostStandardSalary.getSalaryId(), SalPostStandardSalary.getSalaryMoney(), SalPostStandardSalary.getMofDivCode()
        };
        this.getJdbcTemplate().update(sql.toString(), params);
    }

    @Override
    public void updateSalPostStandardSalary(SalPostStandardSalary SalPostStandardSalary) {
        StringBuilder sql = new StringBuilder();
        sql.append("update ").append(TableName.SAL_POST_STANDARD_SALARY);
        sql.append(" set create_time=?, update_time=?, grade_level_id=?, cascade_code=?, type=?, salary_id=?, salary_money=?, mof_div_code=? ")
                .append(" where id = ? ");
        Object[] params = new Object[]{
                SalPostStandardSalary.getCreateTime(), SalPostStandardSalary.getUpdateTime(), SalPostStandardSalary.getGradeLevelId(),
                SalPostStandardSalary.getCascadeCode(), SalPostStandardSalary.getType(), SalPostStandardSalary.getSalaryId(),
                SalPostStandardSalary.getSalaryMoney(), SalPostStandardSalary.getMofDivCode(), SalPostStandardSalary.getId()
        };
        this.getJdbcTemplate().update(sql.toString(), params);
    }

    @Override
    public void deleteSalPostStandardSalary(String id) {
        StringBuilder sql = new StringBuilder();
        sql.append(" delete from ").append(TableName.SAL_POST_STANDARD_SALARY)
                .append(" where id=? ");
        Object[] params = new Object[]{id};
        this.getJdbcTemplate().update(sql.toString(), params);
    }

    @Override
    public List<Map<String, Object>> querySalary(String personTypeId, String postId, String gradeLevelId, String mofDivCode) {
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT ");
        sql.append(" spss.salary_money, ssb.salary_code ");
        sql.append(" FROM ");
        sql.append(TableName.SAL_POST_STANDARD_SALARY).append(" spss ")
                .append(" JOIN ").append(TableName.SAL_POST_GRADE_LEVEL).append(" spgl ")
                .append(" ON spss.grade_level_id = spgl.id ")
                .append(" JOIN ").append(TableName.SALARY_BASEDATA).append(" ssb ")
                .append(" ON spss.salary_id = ssb.id ");
        sql.append(" WHERE ")
                .append(" spgl.person_type_id = ? ")
                .append(" AND spgl.post_id = ? ")
                .append(" AND spgl.id = ? ")
                .append(" AND spss.mof_div_code = ? ");
        Object[] objects = new Object[]{
                personTypeId, postId, gradeLevelId, mofDivCode
        };
        return this.getjdbcTemplate().queryForList(sql.toString(), objects);
    }

    @Override
    public List<SalaryMonthEnd> queryPersonSalary(SalPostStandardSalary salPostStandardSalary) {
        StringBuilder sql = new StringBuilder();
        List<Object> params = new ArrayList<>();
        sql.append(" SELECT ");
        sql.append(" ssme.s1,ssme.s2,ssme.s3,ssme.s4,ssme.s5,ssme.s6,ssme.s7,ssme.s8,ssme.s9,ssme.s10,ssme.s11, ")
                .append(" ssme.s12,ssme.s13,ssme.s14,ssme.s15,ssme.s16,ssme.s17,ssme.s18,ssme.s19,ssme.s20,ssme.s21, ")
                .append(" ssme.s22,ssme.s23,ssme.s24,ssme.s25,ssme.s26,ssme.s27,ssme.s28,ssme.s29,ssme.s30,ssme.s31, ")
                .append(" ssme.s32,ssme.s33,ssme.s34,ssme.s35,ssme.s36,ssme.s37,ssme.s38,ssme.s39,ssme.s40,ssme.s41, ")
                .append(" ssme.s42,ssme.s43,ssme.s44,ssme.s45,ssme.s46,ssme.s47,ssme.s48,ssme.s49,ssme.s50,ssme.s51, ")
                .append(" ssme.s52,ssme.s53,ssme.s54,ssme.s55,ssme.s56,ssme.s57,ssme.s58,ssme.s59,ssme.s60,ssme.s61, ")
                .append(" ssme.s62,ssme.s63,ssme.s64,ssme.s65,ssme.s66,ssme.s67,ssme.s68,ssme.s69,ssme.s70,ssme.s71, ")
                .append(" ssme.s72,ssme.s73,ssme.s74,ssme.s75,ssme.s76,ssme.s77,ssme.s78,ssme.s79,ssme.s80,ssme.s81, ")
                .append(" ssme.s82,ssme.s83,ssme.s84,ssme.s85,ssme.s86,ssme.s87,ssme.s88,ssme.s89,ssme.s90,ssme.s91, ")
                .append(" ssme.s92,ssme.s93,ssme.s94,ssme.s95,ssme.s96,ssme.s97,ssme.s98,ssme.s99,ssme.s100,ssme.mof_div_code, ")
                .append(" ssme.is_thirteen,ssme.person_id,ssme.agency_id,ssme.update_time,ssme.create_time,ssme.id ");
        sql.append(" FROM ").append(TableName.PERSON_INFO).append(" spi ")
                .append(" JOIN ").append(TableName.PERSON_EXT).append(" spe ")
                .append(" ON spi.id = spe.person_id ")
                .append(" JOIN ").append(TableName.SALARY_MONTH_END).append(" ssme ")
                .append(" on ssme.person_id = spi.id ");
        sql.append(" WHERE ")
                .append(" spe.person_type_id = ? ")
                .append(" and spe.post_id = ? ");
        params.add(salPostStandardSalary.getPersonTypeId());
        params.add(salPostStandardSalary.getPostId());
        if ("grade".equals(salPostStandardSalary.getType())) {
            sql.append(" and spe.grade_id = ? ");
            params.add(salPostStandardSalary.getGradeLevelId());
        } else {
            sql.append(" and spe.level_id = ? ");
            params.add(salPostStandardSalary.getGradeLevelId());
        }
        return this.getjdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper<>(SalaryMonthEnd.class), params.toArray());
    }

}
